package com.it.utils;

import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @desc:
 * @author: panxiyi
 * @date: 2022/9/18 12:24 下午
 */
public class ExcelReadUtils {

    public static Map<String,Map<String, Object>> readExcel(File file) throws Exception {
        try {
            //FileInputStream excelFile = new FileInputStream("/Users/mtpxy/Desktop/test/sub9820.xlsx");
            FileInputStream excelFile = new FileInputStream(file);
            Workbook workbook = null;
            String fileName = file.getName();
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
            if (fileType.equals("xls")) {
                workbook = new HSSFWorkbook(excelFile);
            } else if (fileType.equals("xlsx")) {
                workbook = new XSSFWorkbook(excelFile);
            }
           // Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet sheet = workbook.getSheetAt(0);
            Map<String, Map<String, Object>> rowsMap = new HashMap<>();
            int numberOfColumns = sheet.getRow(0).getPhysicalNumberOfCells();
            Iterator<Row> rowIterator = sheet.iterator();
            Row headerRow = rowIterator.next();
            for (int columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) {
                System.out.println("cur i: " + columnIndex + headerRow.getCell(columnIndex).toString());
            }

            List<String> columnNames = Arrays.asList("zh-Hans", "zh-Hant", "en", "ja", "kor", "id", "vi", "th");
            Map<String, String> mapLanguageKey = new HashMap<String, String>(){{
                put("zh-Hans", "zh-Hans");
                put("zh-Hant", "zh-Hant");
                put("en", "en");
                put("ja", "ja");
                put("kor", "kor");
                put("id", "id");
                put("vi", "vi");
                put("th", "th");
                put("英文\n" +
                        "EN", "en");
                put("繁体\n" +
                        "TW", "zh-Hant");
                put("日语\n" +
                        "JA", "ja");
                put("韩语\n" +
                        "KO", "kor");
                put("印尼\n" +
                        "ID", "id");
                put("越南 \n" +
                        "VN", "vi");
                put("泰语\n" +
                        "TH", "th");
            }};
            while (rowIterator.hasNext()) {
                Row currentRow = rowIterator.next();
                Map<String, Object> rowData = new HashMap<>();
                String firstOriginalKey = "";
                for (int columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) {
                    Cell headerCell = headerRow.getCell(columnIndex);
                    String columnName = headerCell.getStringCellValue();
                    columnName = mapLanguageKey.get(columnName);
                    System.out.println("columnName" + columnName);
                    if (!columnNames.contains(columnName)) {
                        continue;
                    }
                    Cell dataCell = currentRow.getCell(columnIndex);
                    if (dataCell != null) {
                        Object cellValue = getCellValue(dataCell);
                        rowData.put(columnName, cellValue);
                        if ("zh-Hans".equals(columnName)) {
                            firstOriginalKey = cellValue.toString();
                        }
                    }
                }
                if (StringUtils.isBlank(firstOriginalKey)) {
                    continue;
                }
                rowsMap.put(firstOriginalKey, rowData);
            }
            // 将数据转换为 JSON
            //ObjectMapper mapper = new ObjectMapper();
            //String json = mapper.writeValueAsString(rowsMap);
            //System.out.println(json);
            //System.out.println(JSON.toJSONString(rowsMap));
            workbook.close();
            return rowsMap;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    private static Object getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    public static void main(String[] args) throws Exception {

        readExcel(new File("/Users/mtpxy/Desktop/test/sub9820.xlsx"));
    }


}
